Amazing SQL!

Postgres Open
2019-09-12

Image by Free-Photos from Pixabay

Who am I

Image by Anemone123 from Pixabay

The SQL Standard

Different

  • Declarative form
  • Indentation
  • carriage return
  • With comments

Turing complete!

Demonstration on PostgreSQL wiki: https://wiki.postgresql.org/wiki/
Turing_Machine_(with_recursive)

Advent of code 2017: https://github.com/xocolatl/advent-of-code/tree/master/2017

Image by philcurtis from Pixabay

History

  • SQL-86
  • SQL-89 cancel and replace SQL-86
  • SQL-92
  • SQL:1999, SQL:2003, SQL:2006
  • SQL:2008, SQL:2011, SQL:2013
  • SQL:2016

pgexercises.com

Getting all members with their recommendee

(external)

Self Join

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"
Image by Nisha Gill from Pixabay

Self Join

  • Table join with itself
  • Available as left, right and internal joins
  • Has near always existed
Image by Nisha Gill from Pixabay

Subquery

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  (
    select
      memref."firstname"
        || ' ' ||
        memref."surname"
    from members as memref
    where
      mem."recommendedby" = memref."memid"
  )
from members as mem
Image by sandid from Pixabay

Subquery

(Inside a Select clause)

  • Displays a subquery result
  • Only for scalar subqueries
  • SQL:1999
Image by sandid from Pixabay
<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>

<select list> ::=
<asterisk>
| <select sublist> [ { <comma> <select sublist> }... ]
Image by sandid from Pixabay
<select sublist> ::=
<derived column>
| <qualified asterisk>


<derived column> ::=
<value expression> [ <as clause> ]
Image by sandid from Pixabay
<value expression> ::=
<numeric value expression>
| <string value expression>
| <datetime value expression>
| <interval value expression>
| <boolean value expression>
| <user-defined type value expression>
| <row value expression>
| <reference value expression>
| <collection value expression>
Image by sandid from Pixabay
<reference value expression> ::=
<value expression primary>

<value expression primary> ::= <parenthesized value expression> | <nonparenthesized value expression primary>
Image by sandid from Pixabay
<nonparenthesized value expression primary> ::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <scalar subquery>
| <case expression>
| <cast specification>
| <subtype treatment>
| <attribute or method reference>
| <reference resolution>
| <collection value constructor>
| <routine invocation>
| <field reference>
| <element reference>
| <method invocation>
| <static method invocation>
| <new specification>
Image by sandid from Pixabay
<scalar subquery> ::= <subquery>

<subquery> ::=
<left paren> <query expression> <right paren>


The degree of a <scalar subquery> shall be 1 (one)
Image by sandid from Pixabay

Subquery

(Inside a Select clause)


select
  ( select
      "columnName"
    from tableName
    where ...
  )
            
Image by sandid from Pixabay

CTE

with memref as
(
  select
    "firstname" || ' ' || "surname" as refname
  from
    members
)
select
  mem."firstname" || ' ' || mem."surname",
  memref.refname
from
  members as mem
left outer join memref
on mem."recommendedby" = memref."memid"

Common Table Expressions (CTE)

  • "Declared" subquery
  • with Clause
  • SQL:1999
<query expression> ::=
[ <with clause> ]
<query expression body>
[ <order by clause> ]
[ <result offset clause> ]
[ <fetch first clause> ]

<with clause> ::=
WITH [ RECURSIVE ] <with list>
<with list> ::=
<with list element> [ { <comma> <with list element> }... ]

<with list element> ::=
<query name> [ <left paren> <with column list> <right paren> ]
AS <table subquery> [ <search or cycle clause> ]

Common Table Expressions (CTE)

with "mySubqueryName"
  (
    column1,
    column2,
    ...
  )
as
  (
    whatever subquery
  )
select
from "mySubqueryName"
...
            
select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"
Image by succo from Pixabay

select
  mem."firstname",
  mem."surname",
  (
    select
      memref."firstname",
      memref."surname",
    from members as memref
    where
      mem."recommendedby" = memref."memid"
)
from members as mem
Image by succo from Pixabay

Lateral join

select
  mem."firstname",
  mem."surname",
    memref."firstname",
  memref."surname"
from members as mem
left join lateral
  (select "firstname",
     "surname"
   from members as mentors
   where
     mentors.memid = mem.recommendedby) as memref
on true
Image by succo from Pixabay

Lateral join

  • Each time you think "For each row, I'd like ..."
  • Highly addictive
  • Since SQL:1999
Image by succo from Pixabay
<query expression> ::=
[ <with clause> ]
<query expression body>


<query expression body> ::=
<non-join query expression>
| <joined table>
Image by succo from Pixabay
<joined table> ::= <cross join>
| <qualified join>
| <natural join>
| <union join>


<qualified join> ::=
<table reference> [ <join type> ] JOIN <table reference>
<join specification>
Image by succo from Pixabay
<table reference> ::=
<table primary> | <joined table>
Image by succo from Pixabay
<table primary> ::=
<table or query name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <lateral derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <collection derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <only spec>
[ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <left paren> <joined table> <right paren>
Image by succo from Pixabay
<lateral derived table> ::=
LATERAL <left paren> <query expression> <right paren>
Image by succo from Pixabay
<join specification> ::=
<join condition>
| <named columns join>


<join condition> ::= ON <search condition>

<search condition> ::=
<boolean value expression>
Image by succo from Pixabay

Getting the list of members who's name is David, Tim or Darren

Image by Hebi B. from Pixabay

Or

select
  "firstname",
  "surname"
from members
where
  "firstname" = 'David'
or
  "firstname" = 'Tim'
or
  "firstname" = 'Darren'
Image by Hebi B. from Pixabay

In

select
  "firstname",
  "surname"
from members
where
  "firstname" in ('David', 'Tim', 'Darren')
Image by Hebi B. from Pixabay

Values

          insert into ("column1",...) values (...);
          values ('Hello world');
Image by Couleur from Pixabay

Values

select
  "firstname",
  "surname"
from members
inner join
  values
    (('David'),('Tim'),('Darren'))
  as people(firstname)
on members."firstname" = people."firstname"
Image by Couleur from Pixabay

Values

  • To create a "constant" table
  • To lower network overhead during Inserts
  • To tune some 'in' where clauses
  • Since SQL:92
Image by Couleur from Pixabay
<query expression> ::=
<non-join query expression>
| <joined table>


<non-join query expression> ::=
<non-join query term>
| <query expression> UNION [ ALL ] [ <corresponding spec> ] <query term>
| <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>
Image by Couleur from Pixabay
<non-join query term> ::=
<non-join query primary>
| <query term> INTERSECT [ ALL ] [ <corresponding spec> ] <query primary>


<non-join query primary> ::=
<simple table>
| <left paren> <non-join query expression> <right paren>
Image by Couleur from Pixabay
<simple table> ::=
<query specification>
| <table value constructor> | <explicit table>


<table value constructor> ::=
VALUES <table value constructor list>
Image by Couleur from Pixabay

Getting the list of members who's never booked an equipment

Image by Terri Cnudde from Pixabay

NOT IN

select surname,
  firstname
from members
where "memid" not in
  (
    select memid
    from bookings
  )
Image by Terri Cnudde from Pixabay

NOT EXISTS

select surname,
  firstname
from members
where not exists
  (
    select 1
    from bookings
    where bookings.memid = members.memid
  )
Image by Terri Cnudde from Pixabay

ANTIJOIN

select surname,
  firstname
from members
left outer join "bookings"
on "members"."memid" = "bookings"."memid"
where "bookings".memid is null
Image by Catkin from Pixabay

For the year 2012, getting for each equipment the duration of use per month, then their total per year and then the total for all equipments for the year

3 queries

select "facid",
  extract(month from starttime), sum(slots)
from "bookings"
where extract(year from starttime) = 2012
group by factid,
  extract(month from starttime)
union
select "facid",
  null, sum(slots)
from "bookings"
where extract(year from starttime) = 2012
group by factid 
union
select null,
  null, sum(slots)
from "bookings"
where extract(year from starttime) = 2012

Rollup

select facid,
  extract(month from starttime),
  sum(slots) as slots
from cd.bookings
where extract(year from starttime) = 2012
group by rollup("facid", extract(month from "starttime"))
order by "facid",
  extract(month from "starttime")

Rollup

  • Hierarchal data
  • Different from CUBE
  • Since SQL:99
<group by clause> ::=
GROUP BY <grouping specification>


<grouping specification> ::=
<grouping column reference>
| <rollup list>
| <cube list>
| <grouping sets list>
| <grand total>
| <concatenated grouping>
<rollup list> ::=
ROLLUP <left paren> <grouping column reference list> <right paren>

For each member, display their first and last names allong with the total number of members in the club

Image by Pexels from Pixabay

Subquery

select (
  select count(*) from members
) as count,
firstname, surname
        from members
order by joindate
Image by sandid from Pixabay

Window functions

select count(*) 
  over(),
  "firstname",
  "surname"
from members
order by joindate 
Image by RD LH from Pixabay

Window functions

  • Data aggregate on non displayed columns
  • SQL:2003
  • SQL:2011: LEAD, LAG...
Image by RD LH from Pixabay
<window function> ::= <window function type> OVER <window name or specification>

<window name or specification> ::=
<window name>
| <in-line window specification>


<in-line window specification> ::= <window specification>
Image by RD LH from Pixabay
<window specification> ::=
<left paren> <window specification details> <right paren>


<window specification details> ::=
[ <existing window name> ]
[ <window partition clause> ]
[ <window order clause> ]
[ <window frame clause> ]
Image by RD LH from Pixabay

For the member who's id is 27, find the chained list of recommenders

Image by Free-Photos from Pixabay

Recursive CTE

with recursive
recommenders(recommender) as (
  select recommendedby from cd.members where memid = 27
  union all
  select mems.recommendedby
  from recommenders recs
  inner join cd.members mems
    on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join cd.members mems
  on recs.recommender = mems.memid
order by memid desc 
Image by andreas N from Pixabay

Conclusion

Feedback

https://postgresql.us/events/pgopen2019/feedback/